package org.unique.support.orm;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.BeanMapHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.unique.commons.utils.CollectionUtil;
import org.unique.ioc.annotation.Component;
import org.unique.support.orm.connection.ConnectionManager;
import org.unique.support.orm.dao.Page;
import org.unique.support.orm.exception.QueryException;
import org.unique.support.orm.exception.UpdateException;

/**
 * 数据库操作类
 * @author biezhi
 * @version 0.1.0
 */
@Component
public class DbUtil {

    private static final Logger logger = LoggerFactory.getLogger(DbUtil.class);

    public static Connection getConn(){
    	Connection conn = ConnectionManager.getConnection();
    	return conn;
    }
    
    protected static QueryRunner getQueryRunner() {
		return new QueryRunner();
	}
    
    /**
     * 查询实体对象
     */
    public static <T> T find(Class<T> clazz, String sql, Object... params) {
        T entity;
        try {
        	logger.debug("Sql:" + sql);
            logger.debug("Parameters:" + Arrays.toString(params));
            entity = getQueryRunner().query(getConn(), sql, new BeanHandler<T>(clazz), params);
        } catch (SQLException e) {
            throw new QueryException(e);
        }
        return entity;
    }
    
    @SuppressWarnings("rawtypes")
    private static ScalarHandler scalarHandler = new ScalarHandler() {  
        @Override  
        public Object handle(ResultSet rs) throws SQLException {  
            Object obj = super.handle(rs);  
            if (obj instanceof BigInteger)  
                return ((BigInteger) obj).longValue();  
            return obj;  
        }  
    }; 
    
    @SuppressWarnings("unchecked")
    public static long count(String sql, Object... params) {
        Number num = 0;
        try {
            num = (Number) getQueryRunner().query(getConn(), sql, scalarHandler, params);
        } catch (SQLException e) {
            throw new QueryException(e);
        }
        return (num != null) ? num.longValue() : -1;
    }

    /**
     * 查询实体list
     */
    public static <T> List<T> findList(Class<T> clazz, String sql, Object... params) {
        List<T> entityList;
        try {
        	logger.debug("Sql:" + sql);
            logger.debug("Parameters:" + Arrays.toString(params));
            entityList = getQueryRunner().query(getConn(), sql, new BeanListHandler<T>(clazz), params);
        } catch (SQLException e) {
            throw new QueryException(e);
        }
        return entityList;
    }

    /**
     * 根据实体分页查询List
     */
    public static <T> Page<T> findListPage(Class<T> clazz, Integer page, Integer pageSize, String sql, Object... params) {
    	//查询总记录数
        Long totleCount = findColumn("select count(1) " + sql.substring(sql.indexOf("from")), params) ;
        Page<T> pageModel = new Page<T>(totleCount, page, pageSize);
        try {
            sql = sql + " LIMIT ?,?";
            List<Object> list = null;
            if (!CollectionUtil.isEmpty(params)) {
            	list = CollectionUtil.newArrayList(params.length + 2);
            	list.addAll(Arrays.asList(params));
            } else{
            	list = CollectionUtil.newArrayList(2);
            }
            list.add(pageModel.getStartIndex());
            list.add(pageModel.getPageSize());
            params = list.toArray();
            logger.debug("Sql:" + sql);
            logger.debug("Parameters:" + Arrays.toString(params));
            List<T> entityList = getQueryRunner().query(getConn(), sql, new BeanListHandler<T>(clazz), params);
            pageModel.setResults(entityList);
        } catch (SQLException e) {
            throw new QueryException(e);
        }
        return pageModel;
    }

    /**
     * 根据实体查询map
     */
    public static <K, V> Map<K, V> findMap(Class<V> clazz, String sql, Object... params) {
        Map<K, V> entityMap;
        try {
        	logger.debug("Sql:" + sql);
            logger.debug("Parameters:" + Arrays.toString(params));
            entityMap = getQueryRunner().query(getConn(), sql, new BeanMapHandler<K, V>(clazz), params);
        } catch (SQLException e) {
            throw new QueryException(e);
        }
        return entityMap;
    }

    /**
     * 查询数组
     */
    public static Object[] findArray(String sql, Object... params) {
        Object[] array;
        try {
        	logger.debug("Sql:" + sql);
            logger.debug("Parameters:" + Arrays.toString(params));
            array = getQueryRunner().query(getConn(), sql, new ArrayHandler(), params);
        } catch (SQLException e) {
            throw new QueryException(e);
        }
        return array;
    }

    /**
     * 查询数组list
     */
    public static List<Object[]> findArrayList(String sql, Object... params) {
        List<Object[]> arrayList;
        try {
        	 logger.debug("Sql:" + sql);
             logger.debug("Parameters:" + Arrays.toString(params));
            arrayList = getQueryRunner().query(getConn(), sql, new ArrayListHandler(), params);
        } catch (SQLException e) {
            throw new QueryException(e);
        }
        return arrayList;
    }

    /**
     * 根据sql查询一个map
     */
    public static Map<String, Object> findMap(String sql, Object... params) {
        Map<String, Object> map;
        try {
        	logger.debug("Sql:" + sql);
            logger.debug("Parameters:" + Arrays.toString(params));
            map = getQueryRunner().query(getConn(), sql, new MapHandler(), params);
        } catch (SQLException e) {
            throw new QueryException(e);
        }
        return map;
    }

    /**
     * 根据sql查询List<Map>
     */
    public static List<Map<String, Object>> findMapList(String sql, Object... params) {
        List<Map<String, Object>> fieldMapList;
        try {
        	logger.debug("Sql:" + sql);
            logger.debug("Parameters:" + Arrays.toString(params));
            fieldMapList = getQueryRunner().query(getConn(), sql, new MapListHandler(), params);
        } catch (SQLException e) {
            throw new QueryException(e);
        }
        return fieldMapList;
    }

    /**
     * 分页查询List<Map>
     */
    public static List<Map<String, Object>> findPage(String sql, Integer page, Integer pageSize, Object... params) {
        List<Map<String, Object>> fieldMapList;
        try {
            sql = sql + " LIMIT ?,?";
            List<Object> list = null;
            if (!CollectionUtil.isEmpty(params)) {
            	list = CollectionUtil.newArrayList(params.length + 2);
            	list.addAll(Arrays.asList(params));
            } else{
            	list = CollectionUtil.newArrayList(2);
            }
            list.add(page);
            list.add(pageSize);
            params = list.toArray();
            logger.debug("Sql:" + sql);
            logger.debug("Parameters:" + Arrays.toString(params));
            fieldMapList = getQueryRunner().query(getConn(), sql, new MapListHandler(), params);
        } catch (SQLException e) {
            throw new QueryException(e);
        }
        return fieldMapList;
    }

    /**
     * 查询一个列
     */
    public static <T> T findColumn(String sql, Object... params) {
        T entity;
        try {
        	logger.debug("Sql:" + sql);
            logger.debug("Parameters:" + Arrays.toString(params));
            entity = getQueryRunner().query(getConn(), sql, new ScalarHandler<T>(), params);
        } catch (SQLException e) {
            throw new QueryException(e);
        }
        return entity;
    }

    /**
     * 查询多列
     */
    public static <T> List<T> findColumnList(String sql, Object... params) {
        List<T> list;
        try {
        	logger.debug("Sql:" + sql);
            logger.debug("Parameters:" + Arrays.toString(params));
            list = getQueryRunner().query(getConn(), sql, new ColumnListHandler<T>(), params);
        } catch (SQLException e) {
            throw new QueryException(e);
        }
        return list;
    }

    /**
     * 将查询的列查为map
     */
    public static <T> Map<T, Map<String, Object>> findColumnMap(String column, String sql, Object... params) {
        Map<T, Map<String, Object>> map;
        try {
        	logger.debug("Sql:" + sql);
            logger.debug("Parameters:" + Arrays.toString(params));
            map = getQueryRunner().query(getConn(), sql, new KeyedHandler<T>(column), params);
        } catch (SQLException e) {
            throw new QueryException(e);
        }
        return map;
    }

    /**
     * 根据sql更新
     */
    public static int update(String sql, Object... params) {
        int result = 0;
        try {
        	logger.debug("Sql:" + sql);
            logger.debug("Parameters:" + Arrays.toString(params));
            result = getQueryRunner().update(getConn(), sql, params);
        } catch (SQLException e) {
            throw new UpdateException(e);
        }
        return result;
    }

    /**
     * 批量更新
     */
    public static int[] updateBatch(String sql, List<Object[]> list) {
        int[] result = {};
        try {
            Object[] param = list.toArray();
            Object[][] params = new Object[param.length][];
            logger.debug("Sql:" + sql);
            logger.debug("Parameters:" + Arrays.toString(list.toArray()));
            result = getQueryRunner().batch(getConn(), sql, params);
        } catch (SQLException e) {
            throw new UpdateException(e);
        }
        return result;
    }
    
}
